package com.example.demo.util;

import com.sun.org.apache.xpath.internal.operations.Bool;
import org.springframework.beans.factory.annotation.Value;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JDBCUtil {
    private Connection conn;
    private Statement stmt;

    private Connection conn2;
    private Statement stmt2;

    private Connection conn3;
    private Statement stmt3;
    public JDBCUtil() {
//        String DB_URL = "jdbc:mysql://localhost:3306/APIPlatform?useUnicode=true&characterEncoding=utf-8";
//        String DB_URL2 = "jdbc:mysql://localhost:3306/APIPlatform?useUnicode=true&characterEncoding=utf-8";
        String DB_URL2 = "jdbc:mysql://10.24.11.134:3306/APIPlatform?useUnicode=true&characterEncoding=utf-8";

//        String DB_URL3 = "jdbc:mysql://localhost:3308/APIPlatform?useUnicode=true&characterEncoding=utf-8";
        String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        String USER = "root";
        String PASS = "ElTo@EW*nIWOt2Tk";

//        String DB_URL = "jdbc:oracle:thin:@//10.24.11.181:1621/helowin";
//        String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
//        String USER = "SJCJ_FGW";
//        String PASS = "SJCJ_FGW";
        try {
            Class.forName(JDBC_DRIVER);
//            conn = DriverManager.getConnection(DB_URL, USER, PASS);
//            conn.setAutoCommit(false);
//            stmt = conn.createStatement();
            conn2 = DriverManager.getConnection(DB_URL2, USER, PASS);
            conn2.setAutoCommit(false);
            stmt2 = conn2.createStatement();
//            conn3 = DriverManager.getConnection(DB_URL3, USER, PASS);
//            conn3.setAutoCommit(false);
//            stmt3 = conn3.createStatement();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Boolean insertTable(String tableName, ArrayList<ArrayList<String>> list , int num){
        try {
            for (int j = 0 ; j < list.size() ; j ++){
                String tmp = "";
                for(int i = 0 ; i < 18; i ++){
                    if(list.get(j).get(i) != null){
                        if(i == 0)
                            tmp += ("'" + list.get(j).get(i) + "'");
                        else
                            tmp += (",'" + list.get(j).get(i) + "'");
                    }
                    else{
                        if(i == 0)
                            tmp += ("'" + "null" + "'");
                        else
                            tmp += (",'" + "null" + "'");
                    }
                }
                String sql = "INSERT INTO t"+tableName+" VALUES("+
                        tmp+
                        ")";
                if(num == 1){
                    stmt.addBatch(sql);
                    if((j+1) % 1000 == 0||j == list.size() - 1){
                        int[] updateCounts = stmt.executeBatch();
                        stmt.clearBatch();
                    }

                }
                else if(num == 2){
                    stmt2.addBatch(sql);
                    int[] updateCounts = stmt2.executeBatch();
                    stmt2.clearBatch();

                }
                else if(num == 3){
                    stmt3.addBatch(sql);
                    if((j+1) % 100 == 0||j == list.size() - 1){
                        int[] updateCounts = stmt3.executeBatch();
                        stmt3.clearBatch();
                    }
                }
            }
            conn2.commit();
            conn2.setAutoCommit(true);//在把自动提交打开
//            conn.commit();
//            conn.setAutoCommit(true);//在把自动提交打开
//            conn3.commit();
//            conn3.setAutoCommit(true);//在把自动提交打开
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }

    public Boolean createTable(Map<String,Object> map, int num){
        try {
            String tmp = "";
            for(int i = 1; i <= ((List)(map.get("list"))).size() ; i ++ ){
                if(i == 1){
                    tmp += (String.valueOf((char)(i + 96))+" VARCHAR(255)");
                }
                else{
                    tmp += (","+String.valueOf((char)(i + 96))+" VARCHAR(255)");
                }
            }
            String sql = "CREATE TABLE t"+map.get("tableName") +
                    "(" +
                    tmp
                    + ")";
            if(num == 1)
                stmt.executeUpdate(sql);
            else if(num == 2)
                stmt2.executeUpdate(sql);
            else if(num == 3)
                stmt3.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return true;
    }


    public Boolean insertData(String tableName, String data , int num){
        if(num == 1){
            try {
                boolean flag = false;
                DatabaseMetaData meta = conn.getMetaData();
                String type [] = {"TABLE"};
                ResultSet rs = meta.getTables(null, null, tableName, type);
                flag = rs.next();
                if(!flag){
                    String sql = "CREAT TABLE "+tableName +
                            " (data LONGTEXT)";
                    stmt.executeUpdate(sql);
                }
                String sql = "INSERT INTO "+tableName+" VALUES('"+ data + "')";
                System.out.println(sql);
                stmt.execute(sql);
                conn.commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        else if(num == 2){
            try {
                boolean flag = false;
                DatabaseMetaData meta = conn2.getMetaData();
                String type [] = {"TABLE"};
                ResultSet rs = meta.getTables(null, null, tableName, type);
                flag = rs.next();
                if(!flag){
                    String sql = "CREAT TABLE "+tableName +
                            " (data LONGTEXT)";
                    stmt2.executeUpdate(sql);
                }
                String sql = "INSERT INTO "+tableName+" VALUES('"+ data + "')";
                System.out.println(sql);
                stmt2.execute(sql);
                conn2.commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        else if(num == 3){
            try {
                boolean flag = false;
                DatabaseMetaData meta = conn3.getMetaData();
                String type [] = {"TABLE"};
                ResultSet rs = meta.getTables(null, null, tableName, type);
                flag = rs.next();
                if(!flag){
                    String sql = "CREAT TABLE "+tableName +
                            " (data LONGTEXT)";
                    stmt3.executeUpdate(sql);
                }
                String sql = "INSERT INTO "+tableName+" VALUES('"+ data + "')";
                System.out.println(sql);
                stmt3.execute(sql);
                conn3.commit();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        return true;
    }


    public boolean Close(){
        try {
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return true;
    }



}
